import datetime
from odps import ODPS

"""
筛选合适的订单数据
"""

odps = ODPS('xxx', 'xxxxxxxxxxx', 'xxx_APP', endpoint='http://service.odps.aliyun.com/api')


sql = """
SELECT scope_id as artisan_id
        ,case SUBSTR(value0,31,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 00_00
        ,case SUBSTR(value0,29,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 00_30
        ,case SUBSTR(value0,27,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 01_00
        ,case SUBSTR(value0,25,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 01_30
        ,case SUBSTR(value0,23,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 02_00
        ,case SUBSTR(value0,21,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 02_30
        ,case SUBSTR(value0,19,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 03_00
        ,case SUBSTR(value0,17,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 03_30
        ,case SUBSTR(value0,15,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 04_00
        ,case SUBSTR(value0,13,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 04_30
        ,case SUBSTR(value0,11,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 05_00
        ,case SUBSTR(value0,9,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 05_30
        ,case SUBSTR(value0,7,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 06_00
        ,case SUBSTR(value0,5,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 06_30
        ,case SUBSTR(value0,3,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 07_00
        ,case SUBSTR(value0,1,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 07_30
        ,case SUBSTR(value1,31,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 08_00
        ,case SUBSTR(value1,29,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 08_30
        ,case SUBSTR(value1,27,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 09_00
        ,case SUBSTR(value1,25,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 09_30
        ,case SUBSTR(value1,23,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 10_00
        ,case SUBSTR(value1,21,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 10_30
        ,case SUBSTR(value1,19,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 11_00
        ,case SUBSTR(value1,17,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 11_30
        ,case SUBSTR(value1,15,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 12_00
        ,case SUBSTR(value1,13,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 12_30
        ,case SUBSTR(value1,11,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 13_00
        ,case SUBSTR(value1,9,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 13_30
        ,case SUBSTR(value1,7,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 14_00
        ,case SUBSTR(value1,5,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 14_30
        ,case SUBSTR(value1,3,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 15_00
        ,case SUBSTR(value1,1,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 15_30
        ,case SUBSTR(value2,31,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 16_00
        ,case SUBSTR(value2,29,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 16_30
        ,case SUBSTR(value2,27,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 17_00
        ,case SUBSTR(value2,25,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 17_30
        ,case SUBSTR(value2,23,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 18_00
        ,case SUBSTR(value2,21,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 18_30
        ,case SUBSTR(value2,19,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 19_00
        ,case SUBSTR(value2,17,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 19_30
        ,case SUBSTR(value2,15,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 20_00
        ,case SUBSTR(value2,13,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 20_30
        ,case SUBSTR(value2,11,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 21_00
        ,case SUBSTR(value2,9,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 21_30
        ,case SUBSTR(value2,7,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 22_00
        ,case SUBSTR(value2,5,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 22_30
        ,case SUBSTR(value2,3,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 23_00
        ,case SUBSTR(value2,1,2) when '00' then -1 when '01' then -1 when '10' then 0  else 1 end as 23_30
FROM xxx_ODS.dw_ar_inventory_time
WHERE ds='20190919' AND SUBSTR(date_tm,1,10)='2019-09-19'
 AND scope='1' AND unit='2'
"""

